package gre.db;

import gre.core.*;
import java.io.*;
import java.sql.*;

/**
 *
 * @author nacuong
 */
public class DBInserter {
  /**
   * Insert some sample datas inside database
   * @param stmt  sql statement executor
   * @throws SQLException
   * @throws IOException
   */
  public static void insertSample(Statement stmt) throws SQLException, IOException {
    WordInserter.insertFormFile(stmt, DBHelper.getCurrentPath() + AutoBackup.backupFile);
  }

  /**
   * Insert a word in to words table
   * @param statement sql statement
   * @param word  the word to be inserted into words table
   */
  public static void insertWord(Statement statement, String word, String meaning) throws SQLException {
    /* check to see whether this word has been stored in Words table */
    ResultSet results = statement.executeQuery("SELECT * FROM words WHERE word='" + word + "'");
    /* insert this word into words table */
    if (!results.next()) {
      meaning = meaning.replaceAll("'", "");
      statement.executeUpdate("INSERT INTO words (word, meaning) VALUES ('" + word + "','" + meaning + "')");
    }
  }

  /**
   * Insert a word and its sentence into sentence table
   * @param statement
   * @param word
   * @param sentence
   */
  public static void insertSentence(Statement statement, String word, String sentence) throws SQLException {
    /* process escape character from sentence */
    sentence = sentence.replaceAll("'", "");

    /* check to see whether this word has been stored in Words table */
    ResultSet results = statement.executeQuery("SELECT * FROM words WHERE word='" + word + "'");

    /* either get wordid or insert the word then get wordid */
    if (!results.next()) {
      statement.executeUpdate("INSERT INTO words (word) VALUES ('" + word + "')");
      results = statement.executeQuery("SELECT * FROM words WHERE word='" + word + "'");
      results.next();
    }
    int wordId = results.getInt("wid");

    /* insert (wordid,statement) into statements table */
    results = statement.executeQuery("SELECT * FROM sentences WHERE wid=" + wordId
            + " AND sentence='" + sentence + "'");
    if (!results.next()) {
      statement.executeUpdate("INSERT INTO sentences VALUES (" + wordId + ",'" + sentence + "')");
    }
  }
}
